為了方便快速寫SQL指令，可以在VS CODE中寫好以後，在其SQL文件點擊右鍵copy path，而後開啟mysql cmd，將複製的路徑貼上，在最前面加上source 即可將我們其路徑的mysql指令複製至cmd中。

ex: source D:\Web\MySQL\CRUD\test.sql

注意:SQL文件的路徑需要用\ 而不是/，且路徑最好不要有空格space或者中文字符。

在SELECT時，可利用as對指定之column取別名。

SELECT first_name as fn , last_name as ln from employee;

+---------+----------+

| fn           | ln       |

+---------+----------+

| Robin   | Jackman  |

| Taylor  | Edward   |

| Vivian  | Dickens  |

| Harry   | Clifford |

| Eliza   | Clifford |

| Nancy   | Newman   |

| Melinda | Clifford |

| Jack    | Chan     |

| Harley  | Gilbert  |

+---------+----------+

9 rows in set (0.01 sec)

WHERE: 當我們資料庫資料太多，又想過濾出自己想要的資料，可以利用where。

SELECT * from employee where title="Software Engineer";

過濾出只屬於title為Software Engineer的row。

+----+------------+-----------+-------------------+--------+------------+-------+

| id | first_name | last_name | title             | salary | hire_date  | notes |

+----+------------+-----------+-------------------+--------+------------+-------+

|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |

|  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  |

|  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |

+----+------------+-----------+-------------------+--------+------------+-------+

3 rows in set (0.01 sec)

OR:可以過濾其中一個符合條件的資料。

mysql> SELECT * from employee where title="Software Engineer" OR salary="5500";;

+----+------------+-----------+-------------------+--------+------------+-------+

| id | first_name | last_name | title             | salary | hire_date  | notes |

+----+------------+-----------+-------------------+--------+------------+-------+

|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |

|  5 | Eliza      | Clifford  | Software Engineer |   4750 | 1998-10-19 | NULL  |

|  6 | Nancy      | Newman    | Software Engineer |   5100 | 2007-01-23 | NULL  |

+----+------------+-----------+-------------------+--------+------------+-------+

AND:如果要增加過濾條件可使用 and ，返回兩者都符合條件的資料。

mysql> SELECT * from employee where title="Software Engineer" AND salary="5500";

+----+------------+-----------+-------------------+--------+------------+-------+

| id | first_name | last_name | title             | salary | hire_date  | notes |

+----+------------+-----------+-------------------+--------+------------+-------+

|  1 | Robin      | Jackman   | Software Engineer |   5500 | 2001-10-12 | NULL  |

+----+------------+-----------+-------------------+--------+------------+-------+

NOT:我們可以使用NOT來過濾掉不要的資料，留下要的資料。

mysql> SELECT * from employee where NOT title="Software Architect";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  4 | Harry      | Clifford  | Database Administrator |   6800 | 2015-12-10 | NULL  |
|  5 | Eliza      | Clifford  | Software Engineer      |   4750 | 1998-10-19 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  7 | Melinda    | Clifford  | Project Manager        |   8500 | 2013-10-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
7 rows in set (0.02 sec)

當然我們也可以混合應用其語法，如AND搭配NOT。

mysql> SELECT * FROM employee WHERE last_name != "Clifford";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  1 | Robin      | Jackman   | Software Engineer      |   5500 | 2001-10-12 | NULL  |
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  6 | Nancy      | Newman    | Software Engineer      |   5100 | 2007-01-23 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
6 rows in set (0.01 sec)

顯示所有lastName非Clifford且title不為Software Engineer的資料。

mysql> SELECT * FROM employee WHERE last_name != "Clifford" AND title !="Software Engineer";
+----+------------+-----------+------------------------+--------+------------+-------+
| id | first_name | last_name | title                  | salary | hire_date  | notes |
+----+------------+-----------+------------------------+--------+------------+-------+
|  2 | Taylor     | Edward    | Software Architect     |   7200 | 2002-09-21 | NULL  |
|  3 | Vivian     | Dickens   | Database Administrator |   6000 | 2012-08-29 | NULL  |
|  8 | Jack       | Chan      | Test Engineer          |   6500 | 2018-09-07 | NULL  |
|  9 | Harley     | Gilbert   | Software Architect     |   8000 | 2000-07-17 | NULL  |
+----+------------+-----------+------------------------+--------+------------+-------+
4 rows in set (0.01 sec)